package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import constants.Constants;
import domain.bw.BodyWeightOpen;

public class BWDao {
	FileDao fileDao = new FileDao();
	
	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;
	
	public BodyWeightOpen getBodyWeightOpen(int fileNo){
		BodyWeightOpen result = new BodyWeightOpen();
		String query = "select Lact, Abs_Adjustment, Pct_Adjustment, MIM1, MIM2, MIM3, MIM4, MIM5, MIM6, MIM7, MIM8, MIM9, MIM10, MIM11, MIM12, MIM13, MIM14, MIM15, MIM16, MIM17, MIM18, MIM19, MIM20, MIM21, MIM22, MIM23, MIM24 from dbo.DVIPV_BODYWEIGHT_OPEN where File_No = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				int lact = rs.getInt("Lact");
				result.getAbsAdjustment()[lact] = rs.getDouble("Abs_Adjustment");
				result.getPctAdjustment()[lact] = rs.getDouble("Pct_Adjustment");
				for(int mim=1; mim<=Constants.MIM; mim++){
					result.getBodyWeightOpen()[lact][mim] = rs.getDouble("MIM"+mim);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[][] getBodyWeightPreg(){
		double result[][] = new double [2+1][Constants.MPREG+1];
		String query = "select Lact, MPreg1, MPreg2, MPreg3, MPreg4, MPreg5, MPreg6, MPreg7, MPreg8, MPreg9 from dbo.DVIP_GLB_BODYWEIGHT_PREG";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			stmt = sqlConnection.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				int lact = rs.getInt("Lact");
				for(int mPreg=1; mPreg<=Constants.MPREG; mPreg++){
					result[lact][mPreg] = rs.getDouble("MPreg"+mPreg);
				}
			}
			rs.close();
			stmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public boolean updateBodyWeightOpen(int fileNo, BodyWeightOpen obj){
		boolean result = true;
		String query = "update dbo.DVIP_IN_BODYWEIGHT_OPEN set Abs_Adjustment = ?, Pct_Adjustment = ?, MIM1 = ?, MIM2 = ?, MIM3 = ?, MIM4 = ?, MIM5 = ?, MIM6 = ?, MIM7 = ?, MIM8 = ?, MIM9 = ?, MIM10 = ?, MIM11 = ?, MIM12 = ?, MIM13 = ?, MIM14 = ?, MIM15 = ?, MIM16 = ?, MIM17 = ?, MIM18 = ?, MIM19 = ?, MIM20 = ?, MIM21 = ?, MIM22 = ?, MIM23 = ?, MIM24 = ? where file_no = ? and lact = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			
			for(int lact=1; lact<=2; lact++){
				int colIndex = 1;
				pStmt.setDouble(colIndex++, obj.getAbsAdjustment()[lact]);
				pStmt.setDouble(colIndex++, obj.getPctAdjustment()[lact]);
				for(int mim=1; mim<=Constants.MIM; mim++){
					pStmt.setDouble(colIndex++, obj.getBodyWeightOpen()[lact][mim]);
				}
				pStmt.setDouble(colIndex++, fileNo);
				pStmt.setDouble(colIndex++, lact);
				
				pStmt.addBatch();
			}
			
			int res[] = pStmt.executeBatch();
			for(int i=0; i<res.length; i++){
				if(res[i]<=0){
					result = false;
				}
			}
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
}
